﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DataBaseUpdate.UpdateDateBase2013
{
    public class UpdateDateBase201311
    {
        public UpdateDateBase201311() { }
        public List<string> list = new List<string>();
        public string Message;//全局变量，装载语句执行后返回信息

        public List<string> ExecuteSQL2013010101(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if NOT EXISTS  (select 1 from  sysobjects where  id = object_id('RmUnit2') and   type = 'U')
CREATE TABLE [dbo].[RmUnit2](
		UnitIdentifier			Varchar(100)		Primary Key		Not NULL,	
		[Floor]						Varchar(100)		NULL,
		FloorCode				Varchar(100)		NULL,
		FloorName				Varchar(100)		NULL,
		UnitCode					Varchar(100)		NULL,
		UnitName				Varchar(100)		NULL,
		UnitFullName			Varchar(100)		NULL,
		DoorTypeIdentifier	Varchar(100)		NULL,

		PavilionIdentifier		Varchar(100)		NULL,
		PavilionCode			Varchar(100)		NULL,
		PavilionName			Varchar(100)		NULL,
		
		BuildingIdentifier	Varchar(100)		NULL,
		BuildingCode			Varchar(100)		NULL,
		BuildingName			Varchar(100)		NULL,
		[OrganIdentifier]		Varchar(100)		NULL
)  ";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[RmUnit2]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013010301(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if NOT EXISTS  (select 1 from  sysobjects where  id = object_id('RmCust') and   type = 'U')
CREATE TABLE [dbo].[RmCust](
	CustIdentifier		Varchar(100)		primary key		not null ,
	CustName			Varchar(100)		NULL,
	CustCode				Varchar(100)		NULL,
	Tel						Varchar(100)		NULL,
	BirthDate				Varchar(100)		NULL,
	Email					Varchar(100)		NULL,
	RelaType				Varchar(100)		NULL,
	UnitIdentifier		Varchar(100)		NULL
)  ";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[RmCust]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013111201(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"--方法名称
if not exists (select * from syscolumns where id = object_id('SysLog') and name='MethodName')
ALTER table SysLog
Add 	MethodName Varchar(500)
--方法所在类
if not exists (select * from syscolumns where id = object_id('SysLog') and name='ClassPath')
ALTER table SysLog
Add 	ClassPath Varchar(500)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("添加[MethodName],[ClassPath]字段到[SysLog]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013111301(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not Exists (select 1 from  sysobjects where  id = object_id('CsValidatePro2') and   type = 'U')
CREATE TABLE [dbo].[CsValidatePro2](
	[ValidateProIdentifier] [varchar](100) NOT NULL,
	[ValidateProName] [varchar](100) NULL,
	[ValidateProCode] [varchar](100) NULL,
	[OrganIdentifier] [varchar](100) NULL,
	[ValidateObjectIdentifier] [varchar](100) NULL,
	[ValidateObjectName] [varchar](100) NULL,
	[ValidateObjectCode] [varchar](100) NULL,
	[ProviderID] [varchar](100) NULL,
	[MaintenanceTime] [varchar](100) NULL,
	[RoomTypeIdentifier] [varchar](100) NULL,
	[RoomTypeCode] [varchar](100) NULL,
	[RoomTypeName] [varchar](100) NULL,
	[DoorTypeIdentifier] [varchar](100) NULL,
	[DoorTypeName] [varchar](100) NULL,
	[DoorTypeCode] [varchar](100) NULL,
	[RoObjectRela] [varchar](100) NOT NULL,
	[DoRoomRela] [varchar](100) NOT NULL,
 CONSTRAINT [PK_CsValidatePro2] PRIMARY KEY CLUSTERED 
(
	[ValidateProIdentifier] ASC,
	[RoObjectRela] ASC,
	[DoRoomRela] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("添加[ValidatePro2]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013111302(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if  exists (select * from syscolumns where id = object_id('TbRmPavilion') and name='App_Identifier')
alter table TbRmPavilion
drop column App_Identifier";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("删除[TbRmPavilion]表中[App_Identifier]字段:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013111303(bool IsExcute)
        {
            list = new List<string>();
            if (IsExcute)
            {
                string SQL = @"if  exists (select * from syscolumns where id = object_id('TbRmBuilding') and name='App_Identifier')
alter table TbRmBuilding
drop column App_Identifier";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("删除[TbRmBuilding]表中[App_Identifier]字段:" + Message);
            return list;
        }

      
    }
}
